<?php
/**
 * Created by PhpStorm.
 * User: DELL
 * Date: 2018/9/5
 * Time: 22:04
 */

namespace app\home\controller;
use app\admin\model\Sign;
use app\home\model\CourseEvaluate;
use app\home\model\CourseSign;
use app\home\model\Homework;
use app\home\model\Summary;
use app\home\model\TeacherNote;
use app\home\model\TeacherNotes;
use app\home\model\WechatUser;
use think\Controller;
use think\Db;

/**
 * Class Day
 * @package app\home\controller
 * 每日统计
 */
class Day extends Controller
{
    /**
     * 统计
     */
    public function index(){
        ini_set('max_execution_time', '300');
        // 每日 9:05 定时执行
        $zero = strtotime(date('Y-m-d')); // 当天开始
        $now = strtotime(date('Y-m-d 09:00:00')); // 当天九点
        $start = strtotime(date('Y-m-d 09:00:00')) - 24*60*60; // 前一天九点
        $res = Db::name('paper')->where(['create_time' => $zero])->field('id')->find();
        if (empty($res)){
            // 已关注
            $sum = WechatUser::where(['subscribe' => 1])->group('openid')->column('openid');
            // 已认证
            $num1 = WechatUser::where(['review_status' => 1])->group('openid')->column('openid');
            // 已认证 学生
            $num2 = WechatUser::where(['review_status' => 1,'role' => 0])->group('openid')->column('openid');
            // 已认证 老师
            $num3 = WechatUser::where(['review_status' => 1,'role' => 1])->group('openid')->column('openid');
            // 签到
            $num4 = Sign::where(['create_time' => ['between',[$zero,$now]]])->group('openid')->column('openid');
            // 总结
            $num5 = Summary::where(['create_time' => ['between',[$start,$now]],'status' => 0])->group('openid')->column('openid');
            // 作业
            $num6 = Homework::where(['create_time' => ['between',[$start,$now]],'status' => 0])->group('openid')->column('openid');
            //总结批阅
            $num7 = TeacherNote::where(['create_time' => ['between',[$start,$now]]])->group('openid')->column('openid');
            // 作业批阅
            $num8 = TeacherNotes::where(['create_time' => ['between',[$start,$now]]])->group('openid')->column('openid');
            // 上课签到
            $num9 = CourseSign::where(['status' => ['in',[1,2]],'signin_time' => ['between',[$start,$now]]])->column('openid');
            // 评价
            $num10 = CourseEvaluate::where(['create_time' => ['between',[$start,$now]]])->group('openid')->column('openid');
            Db::name('paper')->insert(['create_time' => $zero,'sub_sum' => count($sum),'reg_sum' => count($num1),'reg_stu' => count($num2),'reg_tea' => count($num3),'sign_sum' => count($num4),'journal_sum' => count($num5),'homework_sum' => count($num6),'play_sum' => count($num7),'plays_sum' => count($num8),'course_sum' => count($num9),'evaluate_sum' => count($num10)]);
            
            // 所有学校
            $School = WechatUser::where(['review_status' => 1,'role' => 0])->group('school_id')->column('school_id');
            // 签到
            $arr1 = [];
            // 总结
            $arr2 = [];
            // 作业
            $arr3 = [];
            foreach ($School as $value){
                if ($value){
                    // 签到
                    $sum = WechatUser::where(['review_status' => 1,'role' => 0,'school_id' => $value])->count();
                    // 该学校所有学员
                    $sql = "SELECT COUNT(*) AS sum FROM pb_sign AS a LEFT JOIN pb_wechat_user AS b ON a.openid = b.openid WHERE a.create_time BETWEEN {$zero} AND {$now} AND b.review_status = 1 AND b.role = 0 AND b.school_id = {$value}";
                    $num = Db::query($sql);
                    $eq = 0;
                    if ($sum != 0){
                        $eq = $num[0]['sum']/$sum;
                    }
                    if ($eq != 0){
                        $pre = (round($eq,2)*100 > 100) ? 100 : round($eq,2)*100;
                        array_push($arr1,['school_id' => $value,'num' => $num[0]['sum'],'sum' => $sum,'percent' => $pre,'create_time' => $zero]);
                    }
                    // 总结
                    $sqls = "SELECT COUNT(*) AS sum FROM pb_summary AS a LEFT JOIN pb_wechat_user AS b ON a.openid = b.openid WHERE a.create_time BETWEEN {$start} AND {$now} AND b.review_status = 1 AND b.role = 0 AND b.school_id = {$value}";
                    $nums = Db::query($sqls);
                    $eqs = 0;
                    if ($sum != 0){
                        $eqs = $nums[0]['sum']/$sum;
                    }
                    if ($eqs != 0){
                        $pres = (round($eqs,2)*100 > 100) ? 100 : round($eqs,2)*100;
                        array_push($arr2,['school_id' => $value,'num' => $nums[0]['sum'],'sum' => $sum,'percent' => $pres,'create_time' => $zero]);
                    }
                    // 作业
                    $sqlss = "SELECT COUNT(*) AS sum FROM pb_homework AS a LEFT JOIN pb_wechat_user AS b ON a.openid = b.openid WHERE a.create_time BETWEEN {$start} AND {$now} AND b.review_status = 1 AND b.role = 0 AND b.school_id = {$value}";
                    $numss = Db::query($sqlss);
                    $eqss = 0;
                    if ($sum != 0){
                        $eqss = $numss[0]['sum']/$sum;
                    }
                    if ($eqss != 0){
                        $press = (round($eqss,2)*100 > 100) ? 100 : round($eqss,2)*100;
                        array_push($arr3,['school_id' => $value,'num' => $numss[0]['sum'],'sum' => $sum,'percent' => $press,'create_time' => $zero]);
                    }
                }
            }
            // 存储数据库
            if ($arr1){
                Db::table('pb_paper_sign')->insertAll($arr1);  // 签到
            }
            if ($arr2){
                Db::table('pb_paper_journal')->insertAll($arr2);  // 总结
            }
            if ($arr3){
                Db::table('pb_paper_homework')->insertAll($arr3);  // 作业
            }
            // 总结批阅
            $arr4 = [];
            $sql_note = "SELECT COUNT(*) AS sum,openid FROM pb_teacher_note WHERE create_time BETWEEN {$start} AND {$now} GROUP BY openid";
            $Note = Db::query($sql_note);
            foreach ($Note as $value){
                if ($value){
                    array_push($arr4,['create_time' => $zero,'type' => 0,'sum' => $value['sum'],'openid' => $value['openid']]);
                }
            }
            // 存储数据库
            if ($arr4){
                Db::table('pb_paper_play')->insertAll($arr4);
            }
            // 作业批阅
            $arr5 = [];
            $sql_notes = "SELECT COUNT(*) AS sum,openid FROM pb_teacher_notes WHERE create_time BETWEEN {$start} AND {$now} GROUP BY openid";
            $Notes = Db::query($sql_notes);
            foreach ($Notes as $value){
                if ($value){
                    array_push($arr5,['create_time' => $zero,'type' => 1,'sum' => $value['sum'],'openid' => $value['openid']]);
                }
            }
            // 存储数据库
            if ($arr5){
                Db::table('pb_paper_play')->insertAll($arr5);
            }
        }
    }
}